package com.longtop.dbHelper;

import java.io.StringWriter;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import oracle.sql.CLOB;

import org.apache.commons.lang.StringUtils;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.springframework.transaction.annotation.Transactional;

import com.longtop.db.OperatorXML;
import com.longtop.model.Property;
import com.longtop.model.Record;
import com.longtop.model.Table;
import com.longtop.model.Tables;
import com.longtop.sp.mapdao.MapDao;
import com.longtop.sp.mapdao.MapVO;
import com.longtop.sp.mapdao.impl.AbstractOperate;
import com.longtop.sp.mapdao.meta.IPrimaryKey;
public class DbHelper extends AbstractOperate implements IDbHelper{
	
	private  MapDao mapDao = null;

	private OperatorXML ox ;
	private OperatorXML export;
	
	public void setExport(OperatorXML export) {
		this.export = export;
	}
	public void setOx(OperatorXML ox) {
		this.ox = ox;
	}
	public  void setMapDao(MapDao mapDao) {
		this.mapDao = mapDao;
	}
	
	private static String evaluateString(String template, Map<String, Object> data) throws Exception{
		VelocityContext context = new VelocityContext();
		for (String item : data.keySet()) {//遍历查询条件
			context.put(item, data.get(item));
		}
		StringWriter writer = new StringWriter();
		Velocity.evaluate(context, writer, "template", template);
		return writer.toString();
	}
	
	@Override
	public Tables getResultSet(String file_name,String db_type)throws Exception{
		Tables tables = new Tables();
		try{
			tables = (Tables)export.loadObject(file_name);
			List<Table> tableList = tables.getTables();//获取到查询sql的集合
			List<Property> properties = tables.getProperties();//获取到查询sql的条件信息
			//组装properties
			Map<String,Object> data = new HashMap<String, Object>();
			if(properties!=null && properties.size()>0){
				for (int i = 0; i < properties.size(); i++) {
					Property p = properties.get(i);
					data.put(p.getName(), p.getValue());
				}
			}
			if(tableList!=null && tableList.size()>0){
				for (int i = 0; i < tableList.size(); i++) {//遍历所有的查询sql
					Table table = tableList.get(i);//获取单条的查询数据的sql
					String select_sql = table.getSelect_sql();//获取表的
					String table_name = table.getTable_name();//获取表的名称
					String primary_key = getPri(table_name,db_type);//获取表的主键
					String export_whole_table = table.getExport_whole_table();//判断是否导出整张表
					//如果是查询导出整张表的信息的话
					if(!StringUtils.isBlank(export_whole_table) && "true".equalsIgnoreCase(export_whole_table)){
						select_sql = "SELECT * FROM "+table_name+" ORDER BY "+primary_key;//直接生成查询整张表的sql并以主键排序
					}
					if(StringUtils.isBlank(select_sql)){
						System.out.println("请配置<table-name>表名或者<select-sql>语句。");
						continue;
					}
					System.out.println("Export Begin--------------------------------------------------");
					select_sql = DbHelper.evaluateString(select_sql, data);//将查询参数拼接到sql语句中
					table.setSelect_sql(select_sql);
					System.out.println("要导出的第"+(i+1)+"张表的sql语句:"+select_sql);
//					List<Map<String, Object>> re = client.queryForList("query.common_query_for_map", select_sql);
					List<MapVO> re = mapDao.query(select_sql);
					if(re!=null && re.size()>0){
						if(re!=null){
							List<Record> records = new ArrayList<Record>();
							for (int j = 0; j < re.size(); j++) {
								Record record = new Record();
								Map<String,Object> map = re.get(j).getData();
								Map<String,Object> newMap = new LinkedHashMap<String,Object>();
								if(table_name!=null && !table_name.trim().equals("")){
									for(String key :map.keySet()){
										Object value = map.get(key);
										newMap.put(key, value);
									}
								}else{
									for(String key :map.keySet()){
										String newKey = convertColumnName(key);
										Object value = map.get(key);
										if(value instanceof CLOB){
											newMap.put(newKey, oracleClob2Str((CLOB)value));
										}else{
											newMap.put(newKey, value);
										}
									}
								}
								System.out.println(newMap);
								record.setCols(newMap);
								records.add(record);
							}
							table.setRecords(records);
							table.setCount(records.size());
						}
						System.out.println("成功导出"+table_name+"表"+table.getCount()+"条记录。");
					}else{
						System.out.println(table_name+"表没有符合要导出的数据!");
					}
					System.out.println("Export end--------------------------------------------------");
				}
			}
		}catch(Exception e){
			e.printStackTrace();
			throw new Exception("数据导出失败!");
		}
		return tables;
	}
	
	private void backupData(Tables preDatas,String backupFile_name)throws Exception{
		Tables backupTables = new Tables();
		List<Table> tableList = preDatas.getTables();
		List<Table> newTableList = new ArrayList<Table>();
		//没有任何表配置要备份就不生成备份文件
		if(tableList!=null && tableList.size()>0){
			boolean flag = true;
			for (int i = 0; i < tableList.size(); i++) {
				Table table = tableList.get(i);
				if(!StringUtils.isBlank(table.getData_backup()) && table.getData_backup().equalsIgnoreCase("true")){
					flag = false;
					break;
				}
			}
			if(flag){
				return;
			}
		}
		if(tableList!=null && tableList.size()>0){
			for (int i = 0; i < tableList.size(); i++) {
				Table table = tableList.get(i);
				if(StringUtils.isBlank(table.getData_backup()) || !table.getData_backup().equalsIgnoreCase("true")){
					continue;
				}
				String select_sql = table.getSelect_sql();
				String table_name = table.getTable_name();
				Table newTable = new Table();
				List<MapVO> re = mapDao.query(select_sql);
				if(re!=null && re.size()>0){
					if(re!=null){
						List<Record> records = new ArrayList<Record>();
						for (int j = 0; j < re.size(); j++) {
							Record record = new Record();
							Map<String,Object> map = re.get(j).getData();
							Map<String,Object> newMap = new LinkedHashMap<String,Object>();
							if(table_name!=null && !table_name.trim().equals("")){
								for(String key :map.keySet()){
									Object value = map.get(key);
									newMap.put(key, value);
								}
							}else{
								for(String key :map.keySet()){
									String newKey = convertColumnName(key);
									Object value = map.get(key);
									if(value instanceof CLOB){
										newMap.put(newKey, oracleClob2Str((CLOB)value));
									}else{
										newMap.put(newKey, value);
									}
								}
							}
							record.setCols(newMap);
							records.add(record);
						}
						newTable.setTable_name(table_name);
						newTable.setSelect_sql(select_sql);
						newTable.setInsert_sql(table.getInsert_sql());
						newTable.setData_backup(table.getData_backup());
						newTable.setExport_whole_table(table.getExport_whole_table());
						newTable.setPrimary_key(table.getPrimary_key());
						newTable.setRecords(records);
						newTable.setCount(records.size());
					}
				}
				newTableList.add(newTable);
			}
		}
		backupTables.setTables(newTableList);
		export.saveObject(backupTables, backupFile_name);
		//删除备份数据
		this.deleteData(backupFile_name);
	}
	
	public static String oracleClob2Str(CLOB clob) throws Exception {
        return (clob != null ? clob.getSubString(1, (int) clob.length()) : null);
    }
	
	@Override
	@Transactional(rollbackFor = Exception.class)
	public void deleteData(String file_name) throws Exception {
		// TODO Auto-generated method stub
		if(StringUtils.isBlank(file_name)){
			return;
		}
		Tables tables = (Tables)ox.loadObject(file_name);//查找要删除的数据
		//删除
		List<Table> tableList = tables.getTables();
		if(tableList==null || tableList.size()<=0){
			return ;
		}
		for (int i = 0; i < tableList.size(); i++) {
			Table table = tableList.get(i);
			String table_name = table.getTable_name();
			String data_backup = table.getData_backup();
			if(StringUtils.isBlank(data_backup) || !data_backup.equalsIgnoreCase("true")){
				continue;
			}
			List<Record> records = table.getRecords();
			if(records==null || records.size()<=0){
				continue;
			}
			for (int j = 0; j < records.size(); j++) {
				Map<String,Object> map = records.get(j).getCols();
				MapVO vo = new MapVO(table_name, map);
				mapDao.delete(vo);
			}
			System.out.println("备份"+table_name+"表"+records.size()+"条记录!");
		}
	}
	

	/* (non-Javadoc)
	 * @see com.longtop.dbHelper.IDbHelper#insertData(java.lang.String)
	 */
	@Override
	@Transactional(rollbackFor = Exception.class)
	public void insertData(String file_name,String db_type)throws Exception{
		Tables tables = (Tables)ox.loadObject(file_name);
		List<Table> tableList = tables.getTables();
		if(tableList==null || tableList.size()<=0){
			return ;
		}
//		//先备份数据
		String backupFile_name = getBackUpFileName(file_name);
		backupData(tables,backupFile_name);
		//导入数据
		for (int i = 0; i < tableList.size(); i++) {
			Table table = tableList.get(i);
			String table_name = table.getTable_name();
			String primary_key = getPri(table_name,db_type);
			List<Record> records = table.getRecords();
			if(records==null || records.size()<=0){
				continue;
			}
			int insertCount = 0;
			int updateCount = 0;
			System.out.println("Import Begin--------------------------------------------------");
			for (int j = 0; j < records.size(); j++) {
				//如果数据库中存在当前记录，则修改，否则就添加
				Map<String,Object> map = records.get(j).getCols();
				MapVO vo = new MapVO(table_name, map);
				//数据库存在的当前记录
				Map<String,Object> value = mapDao.queryMapByPrimary(table_name, primary_key, (String)map.get(primary_key));
				if(value!=null){
					mapDao.update(vo);updateCount++;
				}else{
					mapDao.add(vo);insertCount++;
				}
			}
			System.out.println("成功导入"+table_name+"表"+records.size()+"条记录!");
			System.out.print("更新"+updateCount+"条记录，");
			System.out.println("添加"+insertCount+"条记录。");
			System.out.println("Import end--------------------------------------------------");
		}
	}
	
	private String getBackUpFileName(String file_name){
		String backupFile_name = "";
		String start = file_name.substring(0, file_name.lastIndexOf(".xml"));
		String end = start.substring(start.lastIndexOf("\\"));
		start = start.substring(0, start.lastIndexOf("\\"));

		SimpleDateFormat fomart = new SimpleDateFormat("yyyyMMddHHmmss");
		backupFile_name = start + "/backup" + end + "_backup_" + fomart.format(new Date()) + ".xml";
		return backupFile_name;
	}
	
	@Override
	@Transactional(rollbackFor = Exception.class)
	public void deleteData(String configFileName,String file_name,String db_type) throws Exception {
		//先导出留备份，再删除。
		Tables tables = getResultSet(configFileName,db_type);//查找要导出的数据
		export.saveObject(tables, file_name);//导出
		//删除
		List<Table> tableList = tables.getTables();
		if(tableList==null || tableList.size()<=0){
			return ;
		}
		for (int i = 0; i < tableList.size(); i++) {
			Table table = tableList.get(i);
			String table_name = table.getTable_name();
			List<Record> records = table.getRecords();
			if(records==null || records.size()<=0){
				continue;
			}
			for (int j = 0; j < records.size(); j++) {
				Map<String,Object> map = records.get(j).getCols();
				MapVO vo = new MapVO(table_name, map);
				mapDao.delete(vo);
			}
			System.out.println("删除"+table_name+"表"+records.size()+"条记录!");
		}
		
	}
	private static String convertColumnName(String columnName) {
		StringBuilder sb = new StringBuilder();
		String[] chars = columnName.toLowerCase().split("");

		boolean upper = false;
		for (int i = 0; i < chars.length; i++) {
			String tmp = chars[i].trim();
			if (tmp.length() == 0)
				continue;
			if (upper) {
				tmp = tmp.toUpperCase();
				upper = false;
			}
			if (tmp.equalsIgnoreCase("_")) {
				tmp = "";
				upper = true;
			}
			sb.append(tmp);
		}
		return sb.toString();
	}

	private static String getInsertSQL(String table_name,Map<String,Object> data){
		StringBuffer sql = new StringBuffer("insert into "+table_name+" (");
		for (String key : data.keySet()) {
			sql.append(key+",");
		}
		sql = new StringBuffer(sql.substring(0, sql.length()-1));
		sql.append(") values(");
		for (String key : data.keySet()) {
			String value = null;
			if(data.get(key)!=null){
				value = data.get(key).toString();
				value = value.replaceAll("'", "''");
			}
			sql.append("'"+value+"',");
		}
		sql = new StringBuffer(sql.substring(0, sql.length()-1));
		sql.append(")");
		System.out.println(sql.toString());
		return sql.toString();
	}
	@Override
	public int executeImpl() throws Exception {
		return 0;
	}
	
	private String getPri(String tableName, String db_type)throws Exception{
		IPrimaryKey pk = this.pkFactory.createPrimarykey(jdbcTemplate,
				tableName,db_type);
		return pk.getPrimaryKey(0);
	}
	
	
}
